#!usr/bin/env python3
#数据库模型

from flask_sqlalchemy import SQLAlchemy
# encodePassWord 加密密码(password, method=pbkdf2:sha1, salt_length=8)
# checkPassWord 检查加密的密码和输入的密码是否一致(hash,password)
from app.utils.werkzeugUtil import encodePassWord,checkPassWord
from app import db
from flask_login import UserMixin,AnonymousUserMixin
from flask import current_app,url_for
from itsdangerous import TimedJSONWebSignatureSerializer as Serializer #一个可以生成验证令牌的包
from . import login_manager
from datetime import datetime
from markdown import markdown
from app.utils.dtUtil import utcToLocal,getShowDT
import bleach


#用户加载的时候登陆认证管理器认证的回调
@login_manager.user_loader
def load_user(user_id):
	return User.query.get(user_id) #返回查询用户表的结果


#flask_sqlalchemy框架必须有主键id
class User(UserMixin,db.Model):
	__tablename__ = 'users'
	id = db.Column(db.Integer,primary_key=True) #设置id字段 ，并且设置为主键	
	email = db.Column(db.String(64),unique=True,index=True) #用的邮件字段,唯一字段，添加索引
	username = db.Column(db.String(64),unique=True,index=True) #设置name字段(最大长度64) ，并且设置为唯一键
	privilege = db.Column(db.SmallInteger,default=0) #权限值,超级管理员:9 ,管理员:8,普通用户:0
	role_id = db.Column(db.Integer,db.ForeignKey("roles.id")) #设置外键
	password_hash = db.Column(db.String(128))
	confirmed = db.Column(db.Boolean,default=False)
	name = db.Column(db.String(64)) #用户真实姓名
	location = db.Column(db.String(64)) #所在地
	about_me = db.Column(db.Text()) #自我介绍或者说简介
	member_since = db.Column(db.DateTime,default=datetime.utcnow) #注册时间，记录的是格林威治时间
	last_seen = db.Column(db.DateTime,default=datetime.utcnow) #上次登录时间
	posts = db.relationship('Post',backref='author',lazy="dynamic") #关联博客文章 返回的是一条查询
	#关联用户发表过的评论 返回的是一条查询,获取所有:user.comments.all()
	comments = db.relationship('Comments',backref='author',lazy='dynamic') 

	def __init__(self,**kwargs):
		super(User,self).__init__(**kwargs)
		if self.role is None:
			if self.email==current_app.config['MAIL_USERNAME']:#管理员账户
				self.role = Role.query.filter_by(permission=Permission.ADMIN).first()
			if self.role is None:#普通用户
				self.role = Role.query.filter_by(default=True).first()

	@property
	def password(self):
		raise AttributeError("不能直接读取密码!!")

	@password.setter
	def password(self,password):
		self.password_hash = encodePassWord(password)

	#验证用户输入的密码是否正确
	def verify_password(self,password):
		return checkPassWord(self.password_hash,password)

	#检查token
	def check_token(self,token,secret_key=''):
		if secret_key=="":
			secret_key = current_app.config['SECRET_KEY']
		s = Serializer(secret_key)
		try:
			data = s.loads(token)
		except Exception as e:			
			return False
		confirm = data.get('confirm')		
		if confirm!=self.id:			
			return False
		return True

	#创建一个根据id验证的token
	def make_id_token(self,expiration=3600,secret_key=''):
		if secret_key=="":
			secret_key = current_app.config['SECRET_KEY']		
		s = Serializer(secret_key,expiration)
		return s.dumps({'confirm':self.id})

	#验证api的授权token,返回user实例
	@staticmethod
	def verify_auth_token(token,secret_key=''):
		#token = token.encode('utf-8')
		if secret_key=="":
			secret_key = current_app.config['SECRET_KEY']
		s = Serializer(secret_key)
		print(" verify_auth_token",token)
		try:
			data = s.loads(token)
		except Exception as e:
			print(" verify_auth_token Exception",e)		
			return None
		uid = data.get('confirm')
		u = User.query.get(uid)
		print(" verify_auth_token Exception u ",u)
		return u 

	#验证邮件确认的链接	
	def confirm(self,token,secret_key=''):
		'''
		if secret_key=="":
			secret_key = current_app.config['SECRET_KEY']
		s = Serializer(secret_key)
		try:
			data = s.loads(token)
		except Exception as e:
			return False
		if data.get('confirm')!=self.id:
			return False
		'''
		if not self.check_token(token,secret_key):
			return False			
		self.confirmed = True
		commit([self])
		return True
	def can(self,permission):
		return self.role is not None and (self.role.permission & permission)==permission
	def is_admin(self):
		return self.can(Permission.ADMIN)
	def ping(self): #每次收到用户的请求时都要执行该函数刷新上次登录时间
		self.last_seen = datetime.utcnow()
		commit([self])
		
	def to_json(self):
		json_data = {
			"url":url_for('api.get_user',id=self.id,_external=True),
			"username":self.username,
			"posts":url_for('api.get_user_post',id=self.id,_external=True),
			"location":self.location,
			"member_since":getShowDT(utcToLocal(self.member_since)) if self.member_since  else "?",
			"last_seen":getShowDT(utcToLocal(self.last_seen)) if self.last_seen else "?",
			"posts_count":self.posts.count()
		}
		return json_data

	@staticmethod
	def gernerate_fate(count=100): #在数据库中插入大量数据用于测试
		from sqlalchemy.exc import IntegrityError
		from random import seed
		import forgery_py
		seed()
		internet = forgery_py.internet
		for i in range(count):
			u = User(email=internet.email_address(),
					username = internet.user_name(True),
					password = forgery_py.lorem_ipsum.word(),
					confirm = True,
					name = forgery_py.name.full_name(),
					location = forgery_py.address.city(),
					about_me = forgery_py.lorem_ipsum.sentence(),
					member_since = forgery_py.date.date(True)
				)
			db.session.add(u)
			try:
				db.session.commit()
			except IntegrityError:
				db.session.rollback()
		print("user gernerate_fate is OK! .... ")

	def __repr__(self):
		return '<User>=>%r'%self.username

class AnonymousUser(AnonymousUserMixin):#设置匿名用户(游客)
	def can(self):
		return False
	def is_admin(self):
		return False

login_manager.anonymous_user = 	AnonymousUser #当有人流量网页的时候设置默认的用户为匿名用户 

class Role(db.Model):
	__tablename__ = 'roles'
	id = db.Column(db.Integer,primary_key=True) #设置id字段 ，并且设置为主键
	name = db.Column(db.String(64),unique=True) #设置name字段(最大长度64) ，并且设置为唯一键
	default = db.Column(db.Boolean,default=False,index=True) #暂时不知道作者的用意 (2016年11月20日11:13:54)
	permission = db.Column(db.Integer) #角色权限设置,用位操作记录的,对应 Permission 类的定义
	#设置和user表的关系,将会返回与role相关的user列表(list),backref='role' 表示向User模型中添加一个role属性
	users = db.relationship('User',backref='role',lazy='dynamic')
	
	@staticmethod  
	def insert_roles(): #定义一个静态方法用来插入角色表数据 (注意静态方法不需要添加self参数)
		roles = {
			'User':(
				Permission.FOLLOW|
				Permission.COMMENT|
				Permission.WRITE_ARTICLE
				,True), #普通用户
			'Moderator':(
				Permission.FOLLOW|
				Permission.COMMENT|
				Permission.WRITE_ARTICLE |
				Permission.MODERATE_COMMENT
				,False),#协管员
			'admin':(0xff,False),#超级管理员(默认使用前5位,所以超级管理员的权限二进制值为11111转换为16进制0xff)
		}
		for r in roles:
			role = Role.query.filter_by(name=r).first() #查询角色是否存在
			if role is None:
				role = Role(name=r)
			role.permission = roles[r][0]
			role.default = roles[r][1]
			db.session.add(role)
		db.session.commit()



	def __repr__(self):
		return '<Role>=>%r'%self.name
class Post(db.Model): #博客文章表
	__tablename__ = "posts"
	id = db.Column(db.Integer(),primary_key=True)
	title = db.Column(db.String(30))
	title_html = db.Column(db.String(30))
	body = db.Column(db.Text())
	body_html = db.Column(db.Text())
	timestamp = db.Column(db.DateTime,index=True,default=datetime.utcnow)	
	author_id = db.Column(db.Integer(),db.ForeignKey('users.id')) #作者的id 设置外键关联
	#该博客的所有评论,backref='post' 为每条评论数据设置一个变量名为post的字段 表示Post实例
	comments = db.relationship('Comments',backref='post',lazy='dynamic')

	def make_detail_token(self,expiration=3600,secret_key=''):
		if secret_key=="":
			secret_key = current_app.config['SECRET_KEY']		
		s = Serializer(secret_key,expiration)
		return s.dumps({'confirm':self.id})
		
	def confirm_token(self,token,secret_key=''):
		if secret_key=="":
			secret_key = current_app.config['SECRET_KEY']
		s = Serializer(secret_key)
		try:
			data = s.loads(token)
		except Exception as e:
			return False
		if data.get('confirm')!=self.id:
			return False		
		return True
	#获取可以显示的评论的数目
	def getCommentCount(self):
		return self.comments.filter_by(disabled=True).count()

	def to_json(self):		
		json_data = {
			"url":url_for('api.get_post_by_id',id=self.id,_external=True),
			"body":self.body,
			"body_html":self.body_html,
			"timestamp":getShowDT(utcToLocal(self.timestamp)),
			"author":url_for("api.get_user",id=self.author.id,_external=True),
			"comments":url_for('api.get_posts_comments',id=self.id,_external=True),
			"comments_count":self.getCommentCount()
		}
		return json_data

	def getDisabledComments(self):
		return self.comments.filter_by(disabled=True).all()

	@staticmethod	
	def gernerate_fate(): #创建多条假的博客数据
		from random import seed,randint
		import forgery_py
		seed()
		user_count = User.query.count()
		for i in range(user_count):
			u = User.query.offset(randint(0,user_count-1)).first() #随机偏移,每次回去不同的随机用户
			title = forgery_py.lorem_ipsum.title()
			title = title[0:30]
			p = Post(title=title,
					body=forgery_py.lorem_ipsum.sentences(randint(1,3)),
					timestamp = forgery_py.date.date(True),author = u
					)
			db.session.add(p)
			db.session.commit()
	
	@staticmethod	
	def on_change_body(target,value,oldValue,initiator):
		allow_tags = ['a', 'abbr', 'acronym', 'b', 'blockquote', 'code',
			'em', 'i', 'li', 'ol', 'pre', 'strong', 'ul',
			'h1', 'h2', 'h3', 'p'] #允许显示的html标签
		target.body_html = bleach.linkify(bleach.clean(markdown(value,output_format='html'),
			tags=allow_tags,strip=True))
	def on_change_title(target,value,oldValue,initiator):
		allow_tags= ['h1','h2','h3','4']
		target.title_html = bleach.linkify(bleach.clean(markdown(value,output_format='html'),
			tags=allow_tags,strip=True))

db.event.listen(Post.body,'set',Post.on_change_body)
db.event.listen(Post.title,'set',Post.on_change_title)

class Comments(db.Model):
	__tablename__ = 'comments'
	id = db.Column(db.Integer,primary_key=True)
	body = db.Column(db.Text())
	body_html = db.Column(db.Text())
	disabled = db.Column(db.Boolean,default=True)
	author_id = db.Column(db.Integer,db.ForeignKey('users.id')) #该外键为改实例添加了author属性
	post_id = db.Column(db.Integer,db.ForeignKey('posts.id')) #该外键为改实例添加了post属性
	timestamp = db.Column(db.DateTime,default=datetime.utcnow)


	def to_json(self):
		json_data={
			"url":url_for("api.get_posts_comments",id=self.author.id),
			"body":self.body,
			"body_html":self.body_html,
			"timestamp":getShowDT(utcToLocal(self.timestamp)),
			"author":url_for("api.get_user",id=self.author.id)
		}
		return json_data

	def __repr__(self):
		return "Comments<%s,body=%s>"%(self.author.name,self.body)

	@staticmethod	
	def on_change_body(target,value,oldValue,initiator):
		allow_tags = ['a', 'abbr', 'acronym', 'b', 'blockquote', 'code',
			'em', 'i', 'li', 'ol', 'pre', 'strong', 'ul',
			'h1', 'h2', 'h3', 'p'] #允许显示的html标签
		target.body_html = bleach.linkify(bleach.clean(markdown(value,output_format='html'),
			tags=allow_tags,strip=True))

db.event.listen(Comments.body,'set',Comments.on_change_body)

class Permission(object):
	FOLLOW = 0x01 #具有关注其他用户的权限
	COMMENT = 0x02 #具有评论回复他人帖子的权限
	WRITE_ARTICLE = 0x04 #具有撰写文章的权限
	MODERATE_COMMENT = 0x08 #管理他人发表的评论
	ADMIN           = 0x80 #超级管理员,具有一切权限


#增加或者修改都可以用这个函数 dataList是一个list,方便一次操作多条
def commit(dataList):
	#db.session.add() #一次添加一条
	db.session.add_all(dataList) #一次添加多条,只是添加到数据库会话
	db.session.commit()	#真正把数据提交到数据里面

#删除行
def delete(data):
	db.session.delete(data)
	db.session.commit()

#查询表中所有数据
def selectAll(model):
	return model.query.all()

#根据条件查询返回符合条件的所有数据和原始sql语句
def selectBy(model,**condition):
	query = model.query.filter_by(**condition)
	rets = query.all()
	sql = str(query)
	return rets,sql

#返回符合条件的第一条数据和原始的sql语句
def selectOne(model,**condition):
	query = model.query.filter_by(**condition)
	ret = query.first()
	return ret,str(query)